package org.maggioni.springsandbox.rehearsals.e06jdbc.e01basic.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.maggioni.springsandbox.rehearsals.e06jdbc.e01basic.model.Job;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Component;

@Component
public class JdbcDaoImpl {
	
	//private DataSource dataSource;
	private JdbcTemplate jdbcTemplate;
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	
	public int getJobCount() {
		return jdbcTemplate.queryForInt("select count(*) from jobs");
	}

	public String getJobTitle(String jobId) {
		String sql = "select job_title from jobs where job_id=?";
		return jdbcTemplate.queryForObject(sql, new Object[]{jobId}, String.class);
	}
	
	public Job getJob(String jobId) {
		String sql = "select job_id,job_title,min_salary,max_salary from jobs where job_id=?";
		return jdbcTemplate.queryForObject(sql, new Object[]{jobId}, new JobMapper());
	}
	
	public List<Job> getAllJobs() {
		String sql = "select job_id,job_title,min_salary,max_salary from jobs";
		return jdbcTemplate.query(sql, new JobMapper());
	}

	public void insertJob(Job job) {
		String sql = "insert into jobs (job_id,job_title,min_salary,max_salary) values (?,?,?,?)";
		jdbcTemplate.update(sql, new Object[]{job.getJobId(),job.getJobTitle(),job.getMinSalary(),job.getMaxSalary()});
	}
	
	public List<Job> getAllJobsBetweenSalary(int min, int max) {
		String sql = "select job_id,job_title,min_salary,max_salary from jobs where min_salary>=:min and max_salary<=:max";
		SqlParameterSource namedSqlParameter = new MapSqlParameterSource()
														.addValue("min", min)
														.addValue("max", max);
		return namedParameterJdbcTemplate.query(sql, namedSqlParameter, new JobMapper());
	}

	public void createATable() {
		boolean doRun = false;
		if (doRun){
			jdbcTemplate.execute("create table prova (...)");
		}
	}


	private static final class JobMapper implements RowMapper<Job> {
		@Override
		public Job mapRow(ResultSet resultSet, int rowNum) throws SQLException {
			return new Job(
					resultSet.getString("job_id"),
					resultSet.getString("job_title"),
					resultSet.getInt("min_salary"),
					resultSet.getInt("max_salary")
					);
		}
	}
	
//	public DataSource getDataSource() {
//		return dataSource;
//	}
	@Autowired
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
		this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
	}
//	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
//		this.jdbcTemplate = jdbcTemplate;
//	}
//	public JdbcTemplate getJdbcTemplate() {
//		return jdbcTemplate;
//	}
	

}
